package jsyf.oa.dao.system;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.entity.system.MessageEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by Maqiuqiu on 2018/7/21.
 */
@Repository
public class MessageDao extends BaseDao {

    public MessageDao() {
        this.iBaseEntity = new MessageEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, MessageEntity.class);
    }

    public List<JSONObject> getMessageForApp(String userId, String dateTime, String readFlag) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_sys_message.CONTENT as content,\n" +
                "\tt_sys_message.ID as messageId,\n" +
                "\tt_sys_message.CREATE_TIME as createTime,\n" +
                "\tt_sys_message.OPEN_TITLE as openTitle,\n" +
                "\tt_sys_message.OPEN_URL as openUrl,\n" +
                "\tt_sys_message_receiver.READ_FLAG as readFlag,\n" +
                "\tt_sys_message_receiver.ID as receiverId\n" +
                "FROM\n" +
                "\t`t_sys_message`\n" +
                "LEFT JOIN t_sys_message_receiver ON t_sys_message_receiver.MESSAGE_ID = t_sys_message.ID\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND LEFT (\n" +
                "\tt_sys_message.CREATE_TIME,\n" +
                "\t10\n" +
                ") = '" + dateTime + "'\n" +
                "AND t_sys_message_receiver.USER_ID = '" + userId + "'\n" +
                "AND t_sys_message.DEL_FLAG = 0\n" +
                "AND t_sys_message.MESSAGE_TYPE = 3\n" +
                "AND t_sys_message_receiver.USER_TYPE = 0\n");
        if (null != readFlag) {
            sql.append("AND t_sys_message_receiver.READ_FLAG=" + readFlag + "\n");
        }
        sql.append("ORDER BY t_sys_message_receiver.READ_FLAG,t_sys_message.CREATE_TIME DESC\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getMessageForUser(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT id FROM v_sys_user WHERE 1=1");
        sql.append(condition);
        return super.queryForJsonList(sql.toString(), params);
    }

    public List<JSONObject> getMessageForSignIn(String signDate, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tmanager.managerId,\n" +
                "\tsignLeave.createTime,\n" +
                "\tIFNULL(signLeave.signState, 2) AS signState\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS managerId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tLEFT JOIN t_sys_role ON t_sys_user.ROLE_ID = t_sys_role.ID\n" +
                "\t\tLEFT JOIN t_sys_role_name ON t_sys_role.ROLE_NAME_ID = t_sys_role_name.ID\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_role_name.`NAME` = '区域经理'\n" +
                "\t\tAND t_sys_user.JOB_STATE != 1\n" +
                "\t) AS manager\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_sign_in.USER_ID AS managerId,\n" +
                "\t\tt_sys_sign_in.CREATE_TIME AS createTime,\n" +
                "\t\t'0' AS signState\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tWHERE\n" +
                "\t\tt_sys_sign_in.SIGN_DATE = '" + signDate + "'\n" +
                "\tUNION ALL\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_leave.USER_ID AS managerId,\n" +
                "\t\t\t'' AS createTime,\n" +
                "\t\t\t'1' AS signState\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_leave\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_leave.LEAVE_DATE = '" + signDate + "'\n" +
                ") AS signLeave ON manager.managerId = signLeave.managerId\n" +
                "WHERE\n" +
                "(\n" +
                "\tsignLeave.createTime > '" + dateTime + "'\n" +
                "\tOR signLeave.signState IS NULL\n" +
                "\t)\n");
        return super.queryForJsonList(sql.toString());
    }

    public List<JSONObject> getMessageForDaQuSignIn(String managerIds, String signDate, String dateTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tmanager.managerId,\n" +
                "\tsignLeave.createTime,\n" +
                "\tIFNULL(signLeave.signState, 2) AS signState\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_user.ID AS managerId\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\t1 = 1\n" +
                "\t\tAND t_sys_user.ID IN (" + managerIds + ")\n" +
                "\t\tAND t_sys_user.JOB_STATE != 1\n" +
                "\t) AS manager\n" +
                "LEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt_sys_sign_in.USER_ID AS managerId,\n" +
                "\t\tt_sys_sign_in.CREATE_TIME AS createTime,\n" +
                "\t\t'0' AS signState\n" +
                "\tFROM\n" +
                "\t\tt_sys_sign_in\n" +
                "\tWHERE\n" +
                "\t\tt_sys_sign_in.SIGN_DATE = '" + signDate + "'\n" +
                "\tUNION ALL\n" +
                "\t\tSELECT\n" +
                "\t\t\tt_sys_leave.USER_ID AS managerId,\n" +
                "\t\t\t'' AS createTime,\n" +
                "\t\t\t'1' AS signState\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_leave\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_leave.LEAVE_DATE = '" + signDate + "'\n" +
                ") AS signLeave ON manager.managerId = signLeave.managerId\n" +
                "WHERE\n" +
                "(\n" +
                "\tsignLeave.createTime > '" + dateTime + "'\n" +
                "\tOR signLeave.signState IS NULL\n" +
                "\t)\n");
        return super.queryForJsonList(sql.toString());
    }
}
